﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;

namespace Synergy
{
    public partial class FormPS : Form
    {
        static bool tags;
        static string PrintName;
        public FormPS()
        {
            InitializeComponent();
        }

        private void splitContainer1_Panel1_Paint(object sender, PaintEventArgs e)
        {

        }

        private void button_search_Click(object sender, EventArgs e)
        {
            System.Data.DataTable tb;
            string sql;

            if (this.dateTimePicker_jsrq.Checked)
            {

            }
            sql = @"  with Temp as(select ID, row_number() over(order by T. purchasearrivaldto_createdtime) as rowno from 
(select distinct PurchaseArrivalDTO.ID, purchasearrivaldto.createdtime as purchasearrivaldto_createdtime  
from [PU_PurchaseArrival] [PurchaseArrivalDTO]
 LEFT OUTER JOIN [PU_PurchaseArrival_b] [Details] on [PurchaseArrivalDTO].id=[Details].idPurchaseArrivalDTO
 LEFT OUTER JOIN [AA_Inventory] [Details.Inventory] on [Details].idinventory=[Details.Inventory].id
 LEFT OUTER JOIN [AA_InventoryClass] [Details.Inventory.InventoryClass] 
 on [Details.Inventory].idinventoryclass=[Details.Inventory.InventoryClass].id
 LEFT OUTER JOIN [AA_Person] [Clerk] on [PurchaseArrivalDTO].idclerk=[Clerk].id
 LEFT OUTER JOIN [AA_Partner] [Partner] on [PurchaseArrivalDTO].idpartner=[Partner].id
 LEFT OUTER JOIN [AA_BusiType] [BusinessType] on [PurchaseArrivalDTO].idbusinesstype=[BusinessType].id
 LEFT OUTER JOIN [eap_enumitem] [InvoiceType] on [PurchaseArrivalDTO].InvoiceType=[InvoiceType].id
 LEFT OUTER JOIN [eap_enumitem] [VoucherState] on [PurchaseArrivalDTO].VoucherState=[VoucherState].id
 
  where  1=1  and  1=1  and PurchaseArrivalDTO.VoucherDate >= N'2017-03-01'  and  1=1 ";
            if (this.comboBox_ksch.Text != "")
            {
                sql += " And [Details.Inventory].Code>= N'" + this.comboBox_ksch.SelectedValue.ToString() + "' ";
            }
            if (this.comboBox_jsch.Text != "")
            {

                sql += " And  [Details.Inventory].Code<= N'" + this.comboBox_jsch.SelectedValue.ToString() + "' ";
            }
            if (this.comboBox_chfl2.SelectedIndex > 0)
            {
                sql += "  And [Details.Inventory.InventoryClass].ID ='" + this.comboBox_chfl2.SelectedValue.ToString() + "'";

            }
            else
            {
                if (this.comboBox_chfl.SelectedIndex > 0)
                {
                    sql += " And [Details.Inventory.InventoryClass].ID in    (select IDSon from AA_ParentInventoryClass where IDParent in ( N'" + this.comboBox_chfl.SelectedValue.ToString() + "'))";

                }
            }

            if (this.comboBox_ksbh.Text !="")
            {
                sql += "  And PurchaseArrivalDTO.Code>= N'" + this.comboBox_ksbh.Text + "' ";
            }
            if (this.comboBox_jsbh.Text!="")
            {
                sql += "  AND PurchaseArrivalDTO.Code<= N'" + this.comboBox_jsbh.Text + "'";
            }
            if (this.dateTimePicker_ksrq.Checked)
            {
                sql += " And PurchaseArrivalDTO.VoucherDate>=CONVERT(DateTime, N'" + this.dateTimePicker_ksrq.Value.ToString("yyyy-MM-dd") + " 00:00:00',120)";
            }
            if (this.dateTimePicker_jsrq.Checked)
            {
                sql += " AND PurchaseArrivalDTO.VoucherDate<=CONVERT(DateTime, N'" + this.dateTimePicker_jsrq.Value.ToString("yyyy-MM-dd") + " 23:59:59',120)";
            }
            if (this.comboBox_ksks.SelectedIndex > 0)
            {
                sql += "  And     [Partner].Code>= N'" + this.comboBox_ksks.SelectedValue.ToString() + "' ";
            }
            if (this.comboBox_jsks.SelectedIndex > 0)
            {
                sql += "    AND [Partner].Code<= N'" + this.comboBox_jsks.SelectedValue.ToString() + "' ";
            }
            sql += @" )  AS T) 
    
    select distinct rowno, [BusinessType].name AS PurchaseArrivalDTO_BusinessType_Name,[PurchaseArrivalDTO].code AS PurchaseArrivalDTO_Code,[PurchaseArrivalDTO].voucherdate AS PurchaseArrivalDTO_VoucherDate,[InvoiceType].Name AS PurchaseArrivalDTO_InvoiceType_Name,[Partner].partnerAbbName AS PurchaseArrivalDTO_Partner_PartnerAbbName,[Clerk].name AS PurchaseArrivalDTO_Clerk_Name,[PurchaseArrivalDTO].maker AS PurchaseArrivalDTO_Maker,[PurchaseArrivalDTO].auditor AS PurchaseArrivalDTO_Auditor,[PurchaseArrivalDTO].origPaymentCashAmount AS PurchaseArrivalDTO_OrigPaymentCashAmount,[PurchaseArrivalDTO].origTotalTaxAmount AS PurchaseArrivalDTO_OrigTotalTaxAmount,[VoucherState].Name AS PurchaseArrivalDTO_VoucherState_Name,[PurchaseArrivalDTO].changer AS PurchaseArrivalDTO_changer,[PurchaseArrivalDTO].isPriceCheck AS PurchaseArrivalDTO_IsPriceCheck,[PurchaseArrivalDTO].id AS PurchaseArrivalDTO_ID,[PurchaseArrivalDTO].isReduceArrival AS PurchaseArrivalDTO_IsReduceArrival,[BusinessType].code AS PurchaseArrivalDTO_BusinessType_Code,[PurchaseArrivalDTO].createdtime AS PurchaseArrivalDTO_CreatedTime,[PurchaseArrivalDTO].ts AS PurchaseArrivalDTO_Ts,[PurchaseArrivalDTO].createdtime,[Clerk].id as Clerk_id,[Partner].id as Partner_id,[BusinessType].id as BusinessType_id
  from [PU_PurchaseArrival] [PurchaseArrivalDTO]
 LEFT OUTER JOIN [PU_PurchaseArrival_b] [Details] on [PurchaseArrivalDTO].id=[Details].idPurchaseArrivalDTO
 LEFT OUTER JOIN [AA_Inventory] [Details.Inventory] on [Details].idinventory=[Details.Inventory].id
 LEFT OUTER JOIN [AA_InventoryClass] [Details.Inventory.InventoryClass] on 
 [Details.Inventory].idinventoryclass=[Details.Inventory.InventoryClass].id
 LEFT OUTER JOIN [AA_Person] [Clerk] on [PurchaseArrivalDTO].idclerk=[Clerk].id
 LEFT OUTER JOIN [AA_Partner] [Partner] on [PurchaseArrivalDTO].idpartner=[Partner].id
 LEFT OUTER JOIN [AA_BusiType] [BusinessType] on [PurchaseArrivalDTO].idbusinesstype=[BusinessType].id
 LEFT OUTER JOIN [eap_enumitem] [InvoiceType] on [PurchaseArrivalDTO].InvoiceType=[InvoiceType].id
 LEFT OUTER JOIN [eap_enumitem] [VoucherState] on [PurchaseArrivalDTO].VoucherState=[VoucherState].id
 inner join Temp on Temp.id= PurchaseArrivalDTO.ID   ";

            tb = pub.U8GetTable(sql);
            this.dataGridView1.DataSource = tb;
            this.dataGridView1.Refresh();

        }

        private void FormPS_Load(object sender, EventArgs e)
        {
            tags = false;
            ComboBoxInitl();
            this.dataGridView1.AutoGenerateColumns = false;
            tags = true;
        }

        private void ComboBoxInitl()
        {
            ///存货类别
            InventoryClassInitl();
            ///单据编号
            PurchaseArrivalInitl();
            ///客商编号
            PartnerInitl();
            ///
            this.dateTimePicker_jsrq.Value = DateTime.Today;
            this.dateTimePicker_ksrq.Value = DateTime.Today;
            this.dateTimePicker_jsrq.Checked = false;
            this.dateTimePicker_ksrq.Checked = false;
        }

        private void PartnerInitl()
        {
            string sql;
            sql = @"select  PartnerDTO.partnerType, [PartnerDTO].id as ID,[PartnerDTO].code as Code,[PartnerDTO].code+' '+[PartnerDTO].name as Name,[PartnerType].Name AS PartnerType_Name,[PriceGrade].Name AS PriceGrade_Name,[Saledepartment].name AS Saledepartment_Name,[Saleman].name AS Saleman_Name,[PartnerDTO].aRBalance AS ARBalance,[PartnerDTO].aPBalance AS APBalance,[PartnerDTO].advRBalance AS AdvRBalance,[PartnerDTO].advPBalance AS AdvPBalance,[PartnerDTO].disabled AS Disabled,[PartnerDTO].idpartnerclass AS PartnerDTO_idpartnerClass,[SaleDepartment].id as SaleDepartment_id,[SaleMan].id as SaleMan_id
 from [AA_Partner] [PartnerDTO]
 LEFT OUTER JOIN [AA_Department] [SaleDepartment] on [PartnerDTO].idsaledepartment=[SaleDepartment].id
 LEFT OUTER JOIN [AA_PartnerAddress] [PartnerAddresDTOs] on [PartnerDTO].id=[PartnerAddresDTOs].idpartner
 LEFT OUTER JOIN [AA_Person] [SaleMan] on [PartnerDTO].idsaleman=[SaleMan].id
 LEFT OUTER JOIN [eap_enumitem] [PartnerType] on [PartnerDTO].PartnerType=[PartnerType].id
 LEFT OUTER JOIN [eap_enumitem] [PriceGrade] on [PartnerDTO].PriceGrade=[PriceGrade].id
 where (( 1=1 and (PartnerDTO.partnerType <>  N'211') and (([PartnerAddresDTOs].IsDefaultAddress=1) or ([PartnerAddresDTOs].IsDefaultAddress is null)) )) AND 1=1 AND (PartnerDTO.disabled = 0 ) ORDER BY PartnerDTO.Code ASC";
            System.Data.DataTable pttb1 = pub.U8GetTable(sql);
            DataRow dr = pttb1.NewRow();
            dr["Code"] = 0;
            pttb1.Rows.InsertAt(dr, 0);
            System.Data.DataTable pttb2 = pttb1.Copy();
            this.comboBox_ksks.DataSource = pttb1;
            this.comboBox_ksks.DisplayMember = "Name";
            this.comboBox_ksks.ValueMember = "Code";

            this.comboBox_jsks.DataSource = pttb2;
            this.comboBox_jsks.DisplayMember = "Name";
            this.comboBox_jsks.ValueMember = "Code";
        }

        private void PurchaseArrivalInitl()
        {
            string sql;
            sql = @"select top 100 [PurchaseArrivalDTO].id AS ID,[PurchaseArrivalDTO].code as Code
 from [PU_PurchaseArrival] [PurchaseArrivalDTO]
 where  (1=2 or 1=1 )  order by [PurchaseArrivalDTO].code asc ";
            System.Data.DataTable patb1 = pub.U8GetTable(sql);
            DataRow dr = patb1.NewRow();
            dr["ID"] = 0;
            patb1.Rows.InsertAt(dr, 0);

            System.Data.DataTable patb2 = patb1.Copy();

            this.comboBox_ksbh.DataSource = patb1;
            this.comboBox_ksbh.DisplayMember = "Code";
            this.comboBox_ksbh.ValueMember = "ID";

            this.comboBox_jsbh.DataSource = patb2;
            this.comboBox_jsbh.DisplayMember = "Code";
            this.comboBox_jsbh.ValueMember = "ID";
        }

        private void InventoryClassInitl()
        {
            string sql;
            sql = @"select [InventoryClassDTO].id,[InventoryClassDTO].id AS Value,[InventoryClassDTO].code +' '+[InventoryClassDTO].name AS Text,[InventoryClassDTO].idparent AS IdParent,[InventoryClassDTO].code as TCode,[InventoryClassDTO].isEndNode AS isEndNode,[InventoryClassDTO].inId AS inId,[InventoryClassDTO].code AS InventoryClassDTO_Code
 from [AA_InventoryClass] [InventoryClassDTO]
 where 
[InventoryClassDTO].idparent is null order by TCode asc";
            System.Data.DataTable chtb = pub.U8GetTable(sql);
            DataRow dr = chtb.NewRow();
            dr["Value"] = 0;
            chtb.Rows.InsertAt(dr, 0);
            this.comboBox_chfl.DataSource = chtb;
            this.comboBox_chfl.DisplayMember = "Text";
            this.comboBox_chfl.ValueMember = "Value";

        }



        private void comboBox_chfl_SelectedIndexChanged(object sender, EventArgs e)
        {
            string sql;
            string chlb;
            chlb = this.comboBox_chfl.SelectedValue.ToString();

            sql = @"select [InventoryClassDTO].id,[InventoryClassDTO].id AS Value,[InventoryClassDTO].code +' '+[InventoryClassDTO].name AS Text,[InventoryClassDTO].idparent AS IdParent,[InventoryClassDTO].code as TCode,[InventoryClassDTO].isEndNode AS isEndNode,[InventoryClassDTO].inId AS inId,[InventoryClassDTO].code AS InventoryClassDTO_Code
 from [AA_InventoryClass] [InventoryClassDTO]
 where 1=1 ";
            if (pub.ToNum(chlb) != 0)
            {
                sql += " and [InventoryClassDTO].idparent =  '" + chlb + "' order by TCode asc";
            }
            //this.comboBox_chfl2.Dispose();
            System.Data.DataTable chtb2 = pub.U8GetTable(sql);
            DataRow dr = chtb2.NewRow();
            dr["Value"] = 0;
            chtb2.Rows.InsertAt(dr, 0);
            this.comboBox_chfl2.DataSource = chtb2;
            this.comboBox_chfl2.DisplayMember = "Text";
            this.comboBox_chfl2.ValueMember = "Value";


        }

        private void InventoryInitl()
        {


            string chlb1, chlb2;
            chlb1 = this.comboBox_chfl.SelectedValue.ToString();
            chlb2 = this.comboBox_chfl2.SelectedValue.ToString();
            string sql;
            sql = @"select  [InventoryDTO].id as ID,[InventoryDTO].code as Code,[InventoryDTO].code+' '+[InventoryDTO].name as Name,[InventoryDTO].specification AS Specification,[InventoryDTO].disabled AS Disabled,[Unit].name AS Unit_Name,(case [InventoryDTO].isSingleUnit when 1 then [InventoryPriceDTOs].RetailPriceNew else case [inventorypricedtos.unit].isMainUnit when 1 then [InventoryPriceDTOs].RetailPriceNew else null end end)AS InventoryPriceDTOs_RetailPriceNew,isnull([CurrentStock].Quantity,0) as Quantity,isnull([CurrentStock].ATPQuantity,0) as ATPQuantity,[InventoryDTO].madeDate AS Madedate,[InventoryDTO].isSingleUnit AS isSingleUnit,[UnitGroup].name AS UnitGroup_Name,[InventoryDTO].idinventoryclass AS InventoryDTO_IdinventoryClass,[Unit].id as Unit_id,[UnitGroup].id as UnitGroup_id
 
                                from [AA_Inventory] [InventoryDTO]
                                LEFT OUTER JOIN (select idinventory ,sum(baseQuantity) as Quantity,sum(ISNULL(BaseQuantity,0)  + ISNULL(PurchaseArrivalBaseQuantity,0) + ISNULL(PurchaseForReceiveBaseQuantity,0) + ISNULL(ProductForReceiveBaseQuantity,0) + ISNULL(TransOnWayBaseQuantity,0) + ISNULL(OtherOnWayBaseQuantity,0) - (ISNULL(SaleDeliveryBaseQuantity,0) + ISNULL(ForSaleDispatchBaseQuantity,0) + ISNULL(MaterialForSendBaseQuantity,0) + ISNULL(TransForDispatchBaseQuantity,0) + ISNULL(OtherForDispatchBaseQuantity,0)) ) as ATPQuantity  
                                From ST_CurrentStock CurrentStock
                                where ((CurrentStock.idwarehouse in (select id from AA_WareHouse where involveatp = 1))  or CurrentStock.idwarehouse is null)
                                group by idinventory  ) [CurrentStock] ON [CurrentStock].idinventory=[InventoryDTO].id  LEFT OUTER JOIN (select AA_InventoryPrice.id,idinventory, tmpUnit.idunitgroup from AA_InventoryPrice inner join AA_Unit tmpUnit on tmpUnit.id = AA_InventoryPrice.idunit
                                where tmpUnit.isMainUnit=1) mainUnitInventoryPrice on mainUnitInventoryPrice.idinventory = [InventoryDTO].id and mainUnitInventoryPrice.idunitgroup = [InventoryDTO].idunitgroup
 LEFT OUTER JOIN [AA_Unit] [Unit] on [InventoryDTO].idunit=[Unit].id
 LEFT OUTER JOIN [AA_InventoryUnitPrice] [InvUnitPriceDTOs] on [InventoryDTO].id=[InvUnitPriceDTOs].idinventory
 LEFT OUTER JOIN [AA_Unit] [InvUnitPriceDTOs.Unit] on [InvUnitPriceDTOs].idunit=[InvUnitPriceDTOs.Unit].id
 LEFT OUTER JOIN [AA_UnitGroup] [UnitGroup] on [InventoryDTO].idunitgroup=[UnitGroup].id
 LEFT OUTER JOIN [AA_InventoryPrice] [InventoryPriceDTOs] on [InventoryDTO].id=[InventoryPriceDTOs].idinventory
 LEFT OUTER JOIN [AA_Unit] [InventoryPriceDTOs.Unit] on [InventoryPriceDTOs].idunit=[InventoryPriceDTOs.Unit].id
 where (( 1=1 and (InventoryDTO.isPurchase = 1 or InventoryDTO.IsLaborCost = 1) and (([InvUnitPriceDTOs.Unit].isMainUnit=1) or (InventoryDTO.isSingleUnit=1)) )) AND 1=1 AND (InventoryDTO.disabled = 0 ) and ([inventorypricedtos.unit].ismainunit is null or ([inventorypricedtos.unit].ismainunit is not null and ( inventorydto.unittype= N'595') or [inventorypricedtos.unit].ismainunit=1 or([inventorypricedtos.unit].ismainunit=0 and [InventoryPriceDTOs].idinventory <> isnull(mainUnitInventoryPrice.idinventory, N'0') and [InventoryPriceDTOs].idunit=(select top 1 idunit from AA_InventoryPrice tmpInvPrice where tmpInvPrice.idinventory=InventoryDTO.id )) )) ";
            if (pub.ToNum(chlb2) != 0)
            {
                sql += " and [InventoryDTO].idinventoryclass ='" + chlb2 + "'";
            }
            else
            {
                if (pub.ToNum(chlb1) != 0)
                {
                    sql += " and [InventoryDTO].idinventoryclass in (select id from [AA_InventoryClass] where [idparent] ='" + chlb1 + "')";
                }
            }


            sql += " ORDER BY [InventoryDTO].Code ASC";
            System.Data.DataTable chtb1 = pub.U8GetTable(sql);
            DataRow dr = chtb1.NewRow();
            dr["Code"] = 0;


            chtb1.Rows.InsertAt(dr, 0);
            System.Data.DataTable chtb2 = chtb1.Copy();

            this.comboBox_ksch.DataSource = chtb1;
            this.comboBox_ksch.DisplayMember = "Name";
            this.comboBox_ksch.ValueMember = "Code";

            this.comboBox_jsch.DataSource = chtb2;
            this.comboBox_jsch.DisplayMember = "Name";
            this.comboBox_jsch.ValueMember = "Code";



        }

        private void comboBox_ksbh_KeyPress(object sender, KeyPressEventArgs e)
        {
            string sql;
            if (e.KeyChar == 13)
            {
                sql = @"select top 100 [PurchaseArrivalDTO].id AS ID,[PurchaseArrivalDTO].code as Code
 from [PU_PurchaseArrival] [PurchaseArrivalDTO]
 where  (1=2 or 1=1 )  and  [PurchaseArrivalDTO].code like '%" + this.comboBox_ksbh.Text.Trim() + "%' order by [PurchaseArrivalDTO].code asc ";
                System.Data.DataTable patb = pub.U8GetTable(sql);

                this.comboBox_ksbh.DataSource = patb;
                this.comboBox_ksbh.DisplayMember = "Code";
                this.comboBox_ksbh.ValueMember = "ID";
            }
        }

        private void comboBox_jsbh_KeyPress(object sender, KeyPressEventArgs e)
        {
            string sql;
            if (e.KeyChar == 13)
            {
                sql = @"select top 100 [PurchaseArrivalDTO].id AS ID,[PurchaseArrivalDTO].code as Code
 from [PU_PurchaseArrival] [PurchaseArrivalDTO]
 where  (1=2 or 1=1 )  and  [PurchaseArrivalDTO].code like '%" + this.comboBox_ksbh.Text.Trim() + "%' order by [PurchaseArrivalDTO].code asc ";
                System.Data.DataTable patb = pub.U8GetTable(sql);

                this.comboBox_jsbh.DataSource = patb;
                this.comboBox_jsbh.DisplayMember = "Code";
                this.comboBox_jsbh.ValueMember = "ID";
            }
        }

        private void comboBox_chfl2_SelectedIndexChanged(object sender, EventArgs e)
        {

            InventoryInitl();
        }

        private void comboBox_chfl_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (e.KeyChar == 13)
            {
                InventoryInitl();
            }
        }

        private void comboBox_chfl2_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (e.KeyChar == 13)
            {
                InventoryInitl();
            }
        }

        private void comboBox_ksch_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (e.KeyChar == 13)
            {
                InventoryInitl1();
            }
        }

        private void InventoryInitl1()
        {
            if (tags == false)
            {
                return;
            }
            string chlb1, chlb2;
            chlb1 = this.comboBox_chfl.SelectedValue.ToString();
            chlb2 = this.comboBox_chfl2.SelectedValue.ToString();
            string sql;
            sql = @"select  [InventoryDTO].id as ID,[InventoryDTO].code as Code,[InventoryDTO].code+' '+[InventoryDTO].name as Name,[InventoryDTO].specification AS Specification,[InventoryDTO].disabled AS Disabled,[Unit].name AS Unit_Name,(case [InventoryDTO].isSingleUnit when 1 then [InventoryPriceDTOs].RetailPriceNew else case [inventorypricedtos.unit].isMainUnit when 1 then [InventoryPriceDTOs].RetailPriceNew else null end end)AS InventoryPriceDTOs_RetailPriceNew,isnull([CurrentStock].Quantity,0) as Quantity,isnull([CurrentStock].ATPQuantity,0) as ATPQuantity,[InventoryDTO].madeDate AS Madedate,[InventoryDTO].isSingleUnit AS isSingleUnit,[UnitGroup].name AS UnitGroup_Name,[InventoryDTO].idinventoryclass AS InventoryDTO_IdinventoryClass,[Unit].id as Unit_id,[UnitGroup].id as UnitGroup_id
 
                                from [AA_Inventory] [InventoryDTO]
                                LEFT OUTER JOIN (select idinventory ,sum(baseQuantity) as Quantity,sum(ISNULL(BaseQuantity,0)  + ISNULL(PurchaseArrivalBaseQuantity,0) + ISNULL(PurchaseForReceiveBaseQuantity,0) + ISNULL(ProductForReceiveBaseQuantity,0) + ISNULL(TransOnWayBaseQuantity,0) + ISNULL(OtherOnWayBaseQuantity,0) - (ISNULL(SaleDeliveryBaseQuantity,0) + ISNULL(ForSaleDispatchBaseQuantity,0) + ISNULL(MaterialForSendBaseQuantity,0) + ISNULL(TransForDispatchBaseQuantity,0) + ISNULL(OtherForDispatchBaseQuantity,0)) ) as ATPQuantity  
                                From ST_CurrentStock CurrentStock
                                where ((CurrentStock.idwarehouse in (select id from AA_WareHouse where involveatp = 1))  or CurrentStock.idwarehouse is null)
                                group by idinventory  ) [CurrentStock] ON [CurrentStock].idinventory=[InventoryDTO].id  LEFT OUTER JOIN (select AA_InventoryPrice.id,idinventory, tmpUnit.idunitgroup from AA_InventoryPrice inner join AA_Unit tmpUnit on tmpUnit.id = AA_InventoryPrice.idunit
                                where tmpUnit.isMainUnit=1) mainUnitInventoryPrice on mainUnitInventoryPrice.idinventory = [InventoryDTO].id and mainUnitInventoryPrice.idunitgroup = [InventoryDTO].idunitgroup
 LEFT OUTER JOIN [AA_Unit] [Unit] on [InventoryDTO].idunit=[Unit].id
 LEFT OUTER JOIN [AA_InventoryUnitPrice] [InvUnitPriceDTOs] on [InventoryDTO].id=[InvUnitPriceDTOs].idinventory
 LEFT OUTER JOIN [AA_Unit] [InvUnitPriceDTOs.Unit] on [InvUnitPriceDTOs].idunit=[InvUnitPriceDTOs.Unit].id
 LEFT OUTER JOIN [AA_UnitGroup] [UnitGroup] on [InventoryDTO].idunitgroup=[UnitGroup].id
 LEFT OUTER JOIN [AA_InventoryPrice] [InventoryPriceDTOs] on [InventoryDTO].id=[InventoryPriceDTOs].idinventory
 LEFT OUTER JOIN [AA_Unit] [InventoryPriceDTOs.Unit] on [InventoryPriceDTOs].idunit=[InventoryPriceDTOs.Unit].id
 where (( 1=1 and (InventoryDTO.isPurchase = 1 or InventoryDTO.IsLaborCost = 1) and (([InvUnitPriceDTOs.Unit].isMainUnit=1) or (InventoryDTO.isSingleUnit=1)) )) AND 1=1 AND (InventoryDTO.disabled = 0 ) and ([inventorypricedtos.unit].ismainunit is null or ([inventorypricedtos.unit].ismainunit is not null and ( inventorydto.unittype= N'595') or [inventorypricedtos.unit].ismainunit=1 or([inventorypricedtos.unit].ismainunit=0 and [InventoryPriceDTOs].idinventory <> isnull(mainUnitInventoryPrice.idinventory, N'0') and [InventoryPriceDTOs].idunit=(select top 1 idunit from AA_InventoryPrice tmpInvPrice where tmpInvPrice.idinventory=InventoryDTO.id )) )) ";
            if (pub.ToNum(chlb2) != 0)
            {
                sql += " and [InventoryDTO].idinventoryclass ='" + chlb2 + "'";
            }
            else
            {
                if (pub.ToNum(chlb1) != 0)
                {
                    sql += " and [InventoryDTO].idinventoryclass in (select id from [AA_InventoryClass] where [idparent] ='" + chlb1 + "')";
                }
            }
            if (this.comboBox_ksch.Text.Trim() != "")
            {
                sql += "and [InventoryDTO].code + [InventoryDTO].name +[InventoryDTO].specification like '%" + this.comboBox_ksch.Text.Trim() + "%'";
            }

            sql += " ORDER BY [InventoryDTO].Code ASC";
            System.Data.DataTable chtb1 = pub.U8GetTable(sql);
            this.comboBox_ksch.DataSource = chtb1;
            this.comboBox_ksch.DisplayMember = "Name";
            this.comboBox_ksch.ValueMember = "Code";

        }

        private void comboBox_jsch_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (e.KeyChar == 13)
            {
                InventoryInitl2();
            }
        }

        private void InventoryInitl2()
        {
            if (tags == false)
            {
                return;
            }
            string chlb1, chlb2;
            chlb1 = this.comboBox_chfl.SelectedValue.ToString();
            chlb2 = this.comboBox_chfl2.SelectedValue.ToString();
            string sql;
            sql = @"select  [InventoryDTO].id as ID,[InventoryDTO].code as Code,[InventoryDTO].code+' '+[InventoryDTO].name as Name,[InventoryDTO].specification AS Specification,[InventoryDTO].disabled AS Disabled,[Unit].name AS Unit_Name,(case [InventoryDTO].isSingleUnit when 1 then [InventoryPriceDTOs].RetailPriceNew else case [inventorypricedtos.unit].isMainUnit when 1 then [InventoryPriceDTOs].RetailPriceNew else null end end)AS InventoryPriceDTOs_RetailPriceNew,isnull([CurrentStock].Quantity,0) as Quantity,isnull([CurrentStock].ATPQuantity,0) as ATPQuantity,[InventoryDTO].madeDate AS Madedate,[InventoryDTO].isSingleUnit AS isSingleUnit,[UnitGroup].name AS UnitGroup_Name,[InventoryDTO].idinventoryclass AS InventoryDTO_IdinventoryClass,[Unit].id as Unit_id,[UnitGroup].id as UnitGroup_id
 
                                from [AA_Inventory] [InventoryDTO]
                                LEFT OUTER JOIN (select idinventory ,sum(baseQuantity) as Quantity,sum(ISNULL(BaseQuantity,0)  + ISNULL(PurchaseArrivalBaseQuantity,0) + ISNULL(PurchaseForReceiveBaseQuantity,0) + ISNULL(ProductForReceiveBaseQuantity,0) + ISNULL(TransOnWayBaseQuantity,0) + ISNULL(OtherOnWayBaseQuantity,0) - (ISNULL(SaleDeliveryBaseQuantity,0) + ISNULL(ForSaleDispatchBaseQuantity,0) + ISNULL(MaterialForSendBaseQuantity,0) + ISNULL(TransForDispatchBaseQuantity,0) + ISNULL(OtherForDispatchBaseQuantity,0)) ) as ATPQuantity  
                                From ST_CurrentStock CurrentStock
                                where ((CurrentStock.idwarehouse in (select id from AA_WareHouse where involveatp = 1))  or CurrentStock.idwarehouse is null)
                                group by idinventory  ) [CurrentStock] ON [CurrentStock].idinventory=[InventoryDTO].id  LEFT OUTER JOIN (select AA_InventoryPrice.id,idinventory, tmpUnit.idunitgroup from AA_InventoryPrice inner join AA_Unit tmpUnit on tmpUnit.id = AA_InventoryPrice.idunit
                                where tmpUnit.isMainUnit=1) mainUnitInventoryPrice on mainUnitInventoryPrice.idinventory = [InventoryDTO].id and mainUnitInventoryPrice.idunitgroup = [InventoryDTO].idunitgroup
 LEFT OUTER JOIN [AA_Unit] [Unit] on [InventoryDTO].idunit=[Unit].id
 LEFT OUTER JOIN [AA_InventoryUnitPrice] [InvUnitPriceDTOs] on [InventoryDTO].id=[InvUnitPriceDTOs].idinventory
 LEFT OUTER JOIN [AA_Unit] [InvUnitPriceDTOs.Unit] on [InvUnitPriceDTOs].idunit=[InvUnitPriceDTOs.Unit].id
 LEFT OUTER JOIN [AA_UnitGroup] [UnitGroup] on [InventoryDTO].idunitgroup=[UnitGroup].id
 LEFT OUTER JOIN [AA_InventoryPrice] [InventoryPriceDTOs] on [InventoryDTO].id=[InventoryPriceDTOs].idinventory
 LEFT OUTER JOIN [AA_Unit] [InventoryPriceDTOs.Unit] on [InventoryPriceDTOs].idunit=[InventoryPriceDTOs.Unit].id
 where (( 1=1 and (InventoryDTO.isPurchase = 1 or InventoryDTO.IsLaborCost = 1) and (([InvUnitPriceDTOs.Unit].isMainUnit=1) or (InventoryDTO.isSingleUnit=1)) )) AND 1=1 AND (InventoryDTO.disabled = 0 ) and ([inventorypricedtos.unit].ismainunit is null or ([inventorypricedtos.unit].ismainunit is not null and ( inventorydto.unittype= N'595') or [inventorypricedtos.unit].ismainunit=1 or([inventorypricedtos.unit].ismainunit=0 and [InventoryPriceDTOs].idinventory <> isnull(mainUnitInventoryPrice.idinventory, N'0') and [InventoryPriceDTOs].idunit=(select top 1 idunit from AA_InventoryPrice tmpInvPrice where tmpInvPrice.idinventory=InventoryDTO.id )) )) ";
            if (pub.ToNum(chlb2) != 0)
            {
                sql += " and [InventoryDTO].idinventoryclass ='" + chlb2 + "'";
            }
            else
            {
                if (pub.ToNum(chlb1) != 0)
                {
                    sql += " and [InventoryDTO].idinventoryclass in (select id from [AA_InventoryClass] where [idparent] ='" + chlb1 + "')";
                }
            }
            if (this.comboBox_jsch.Text.Trim() != "")
            {
                sql += "and [InventoryDTO].code + [InventoryDTO].name +[InventoryDTO].specification like '%" + this.comboBox_jsch.Text.Trim() + "%'";
            }

            sql += " ORDER BY [InventoryDTO].Code ASC";
            System.Data.DataTable chtb2 = pub.U8GetTable(sql);
            this.comboBox_jsch.DataSource = chtb2;
            this.comboBox_jsch.DisplayMember = "Name";
            this.comboBox_jsch.ValueMember = "Code";
        }

        private void checkBox1_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < this.dataGridView1.Rows.Count; i++)
            {
                this.dataGridView1.Rows[i].Cells[1].Value = this.checkBox1.Checked;
            }
        }

        private void button_print_Click(object sender, EventArgs e)
        {
           if( this.printDialog1.ShowDialog()!=DialogResult.OK)
            {
                return ;
            }
            PrintName= this.printDialog1.PrinterSettings.PrinterName;

            for (int i = 0; i < this.dataGridView1.Rows.Count; i++)
            {
                if (this.dataGridView1.Rows[i].Cells["C选择"].Value != null)
                {
                    PrintDocument(this.dataGridView1.Rows[i].Cells["C单据编号"].Value.ToString());
                }
            }
        }
        /// <summary> 
        /// 
        /// </summary>
        /// <param name="p"></param>
        private void PrintDocument(string jinhuodanhao)
        {
            string sql;
            int row,rows;
           
            sql = @"SELECT [进货单号],[进货单行]       ,[进货单主键]      ,[存货主键]      ,[存货编号]
      ,[存货名称]      ,[物料规格]    ,[仓库主键]      ,[仓库编号]      ,[仓库名称]      ,[客商主键]
      ,[客商编号]      ,[客商名称]      ,[主数量]      ,[辅助数量]      ,[批次号]  ,主计量,辅计量  ,制单人,制单日期  ,[是否扫描通过]
       FROM   [View_jinhuodan]";
            sql += " where [进货单号] ='" + jinhuodanhao + "' order by [进货单行] ";
            System.Data.DataTable jhtb = pub.U8GetTable(sql);
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            Workbooks wbks = app.Workbooks;
            _Workbook _wbk = wbks.Add(System.Windows.Forms.Application.StartupPath+"\\PurchaseArrival.xlsx");
            app.Visible = true;
            Sheets shs = _wbk.Sheets;
            //shs.Visible = true;

            //i是要取得的sheet的index

            _Worksheet _wsh = (_Worksheet)shs.get_Item(1);
            _wsh.Cells[1, 1] = pub.GetCode128B(jhtb.Rows[0]["进货单号"].ToString());
            _wsh.Cells[3, 3] = jhtb.Rows[0]["进货单号"].ToString();
            _wsh.Cells[3,5] = "";
            _wsh.Cells[2, 10] = jhtb.Rows[0]["制单日期"].ToString();
            _wsh.Cells[3, 10] = jhtb.Rows[0]["制单人"].ToString();
            _wsh.Cells[4, 3] = jhtb.Rows[0]["客商名称"].ToString();
            _wsh.Cells[4, 10] = DateTime.Now.ToString();
            _wsh.Cells[5, 2] = "";
            _wsh.Cells[5, 6] = "";

            ///先清空
            ///
            for (int m = 8; m <= 27; m++)
            {
                for (int n = 1; n <= 11; n++)
                {
                    _wsh.Cells[m, n] = "";
                }
            }
            row = 1;
            int page, pages;
            rows=jhtb.Rows.Count;
            pages =  rows/ 10;
            if (rows > 10 * pages)
            {
                pages++;
            }
            page = 1;
            for (int i = 0; i < jhtb.Rows.Count; i++)
            {
                ///塞值
                _wsh.Cells[2 * row + 6, 1] = (i+1).ToString();
                _wsh.Cells[2 * row + 6, 2] = jhtb.Rows[i]["存货名称"].ToString() + " " + jhtb.Rows[i]["物料规格"].ToString();///品名+规格
                _wsh.Cells[2 * row + 7, 2] = jhtb.Rows[i]["存货编号"].ToString();///商品代码
                _wsh.Cells[2 * row + 6, 5] = pub.GetCode128B(jhtb.Rows[i]["进货单行"].ToString() + "$" + jhtb.Rows[i]["存货编号"].ToString()); ///行号+存货编号
                _wsh.Cells[2 * row + 6, 7] = jhtb.Rows[i]["主数量"].ToString();///商品代码
                _wsh.Cells[2 * row + 6, 8] = jhtb.Rows[i]["主计量"].ToString();///商品代码
                _wsh.Cells[2 * row + 6, 9] = jhtb.Rows[i]["辅助数量"].ToString();///商品代码
                _wsh.Cells[2 * row + 6, 10] = jhtb.Rows[i]["辅计量"].ToString();///商品代码
                _wsh.Cells[2 * row + 6, 11] = jhtb.Rows[i]["批次号"].ToString();///商品代码


                if (row >= 10)
                {
                    row = 1;
                    _wsh.Cells[1, 9] = "第" + page.ToString() + "页";
                    _wsh.Cells[1, 11] = "共" + pages.ToString() + "页";
                    _wbk.PrintOut(1, 1, 1, false, PrintName, false, false, null);
                    page++;
                    for (int m = 8; m <= 27; m++)
                    {
                        for (int n = 1; n <= 11; n++)
                        {
                            _wsh.Cells[m, n] = "";
                        }
                    }




                }
                else
                {
                    row++;
                }

               
            }
            if (row > 1)
            {
                _wsh.Cells[1, 9] = "第" + pages.ToString() + "页";
                _wsh.Cells[1, 11] = "共" + pages.ToString() + "页";
                _wbk.PrintOut(1, 1, 1, false, PrintName, false, false, null);
            }
            _wbk.Close(false, null, null);
            app.Quit();
            
        }
    }
}
